/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.csit.iap.ws;

import com.csit.iap.domain.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;
import javax.sql.DataSource;

/**
 *
 * @author Kundzia
 */
@WebService(serviceName = "BranchWebServiceWS")
public class BranchWebServiceWS {

    @Resource(name = "BookstoreChainHQDB")
    private DataSource bookstoreChainHQDB;

    /**
     * Web service operation
     */
    @WebMethod(operationName = "showBookCatalogue")
    public List<Book> showBookCatalogue() {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt = con.prepareStatement("select * from BOOK");
            ResultSet res = stmt.executeQuery();
            List<Book> books = new ArrayList<Book>();

            while (res.next()) {
                books.add(createCompleteBook(res.getString("isbn")));
            }

            res.close();
            stmt.close();
            con.close();

            return books;
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (Exception ex) {
            throw new RuntimeException(ex.toString());
        }
    }

    private Book createCompleteBook(String isbn) {
        try {
            Book book = new Book();

            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt_book = con.prepareStatement("select * from BOOK where isbn=?");
            stmt_book.setString(1, isbn);
            ResultSet res_book = stmt_book.executeQuery();

            PreparedStatement stmt_authors = con.prepareStatement("select author_id from BOOKAUTHOR where book_isbn=?");
            stmt_authors.setString(1, isbn);
            ResultSet res_authors = stmt_authors.executeQuery();

            PreparedStatement stmt_pub = null;
            ResultSet res_pub = null;
            PreparedStatement stmt_aut = null;
            ResultSet res_aut = null;
            PreparedStatement stmt_itm = null;
            ResultSet res_itm = null;

            List<Author> authors;

            if (res_book.next()) {

                book.setIsbn(isbn);

                //prepare publisher
                stmt_pub = con.prepareStatement("select * from PUBLISHER where id=?");
                stmt_pub.setInt(1, res_book.getInt("publisher_id"));
                res_pub = stmt_pub.executeQuery();
                if (res_pub.next()) {
                    book.setPublisher(new Publisher(res_pub.getInt("id"), res_pub.getString("name")));
                }

                //prepare authors
                authors = new ArrayList<Author>();
                while (res_authors.next()) {
                    stmt_aut = con.prepareStatement("select * from AUTHOR where id=?");
                    stmt_aut.setInt(1, res_authors.getInt("author_id"));
                    res_aut = stmt_aut.executeQuery();

                    if (res_aut.next()) {
                        authors.add(new Author(res_aut.getInt("id"), res_aut.getString("name"), res_aut.getString("surname")));
                    }
                }
                book.setAuthors(authors);

                //prepare bookitem
                stmt_itm = con.prepareStatement("select * from BOOKITEM where BOOK_ISBN=?");
                stmt_itm.setString(1, isbn);
                res_itm = stmt_itm.executeQuery();
                if (res_itm.next()) {
                    book.setAmount(res_itm.getInt("amount"));
                    book.setPrice(res_itm.getDouble("price"));
                }

                book.setTitle(res_book.getString("title"));
            }

            stmt_pub.close();
            res_pub.close();
            stmt_aut.close();
            res_aut.close();
            stmt_authors.close();
            res_authors.close();
            res_book.close();
            stmt_itm.close();
            res_itm.close();
            stmt_book.close();

            con.close();

            return book;
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (Exception ex) {
            throw new RuntimeException(ex.toString());
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "processOrder")
    public Response processOrder(@WebParam(name = "order") Order order) {
        Response response = new Response();
        List<ResponseItem> rItems = new ArrayList<ResponseItem>();

        for (OrderItem oi : order.getoItems()) {
            ResponseItem rI = new ResponseItem(oi.getBook(), checkAvailability(oi.getBook().getIsbn(), oi.getAmount()), oi.getId());
            rItems.add(rI);
        }
        response.setrItems(rItems);

        return response;
    }

    private int checkAvailability(String book_isbn, int wanted) {
        int available = 0;
        int forsend = wanted;
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt = con.prepareStatement("select amount from BOOKITEM where book_isbn=?");
            stmt.setString(1, book_isbn);
            ResultSet res = stmt.executeQuery();

            if (res.next()) {
                available = res.getInt("amount");
            }

            res.close();
            stmt.close();
            con.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (Exception ex) {
            throw new RuntimeException(ex.toString());
        }

        if (available < wanted) {
            forsend = available;
        }

        removeOrderedBooksFromDB(book_isbn, forsend, available);

        return forsend;
    }

    private void removeOrderedBooksFromDB(String book_isbn, int amount, int available) {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            con.setAutoCommit(false);
            PreparedStatement stmt = con.prepareStatement("update BOOKITEM set amount=? where book_isbn=?");
            stmt.setInt(1, (available - amount));
            stmt.setString(2, book_isbn);
            stmt.executeUpdate();
            stmt.close();
            con.commit();
            con.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (Exception ex) {
            throw new RuntimeException(ex.toString());
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "searchByTitle")
    public List<Book> searchByTitle(@WebParam(name = "title") String title) {
        try {
            List<Book> books = new ArrayList<Book>();
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt_book = con.prepareStatement("select * from BOOK where lcase(title)=?");
            stmt_book.setString(1, title.toLowerCase());
            ResultSet res_book = stmt_book.executeQuery();
            
            while(res_book.next()) {
                books.add(createCompleteBook(res_book.getString("isbn")));
            }
            
            stmt_book.close();
            res_book.close();
            con.close();
            return books;
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "searchByPublisher")
    public List<Book> searchByPublisher(@WebParam(name = "publisher") String publisher) {
        try {
            List<Book> books = new ArrayList<Book>();
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt_pub = con.prepareStatement("select * from PUBLISHER where lcase(name)=?");
            stmt_pub.setString(1, publisher.toLowerCase());
            ResultSet res_pub = stmt_pub.executeQuery();
            PreparedStatement stmt_book = null;
            ResultSet res_book = null;
            
            if(res_pub.next()) {
                stmt_book = con.prepareStatement("select * from BOOK where publisher_id=?");
                stmt_book.setInt(1, res_pub.getInt("id"));
                res_book = stmt_book.executeQuery();
                
                while(res_book.next()) {
                    books.add(createCompleteBook(res_book.getString("isbn")));
                }
            }
            
            stmt_book.close();
            res_book.close();
            stmt_pub.close();
            res_pub.close();
            
            con.close();
            
            return books;
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "searchByIsbn")
    public Book searchByIsbn(@WebParam(name = "isbn") String isbn) {
        try {
            Book book = null;
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt_book = con.prepareStatement("select * from BOOK where isbn=?");
            stmt_book.setString(1, isbn);
            ResultSet res_book = stmt_book.executeQuery();
            
            while(res_book.next()) {
                book = createCompleteBook(res_book.getString("isbn"));
            }
            
            stmt_book.close();
            res_book.close();
            con.close();
            return book;
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        }
    }

    /**
     * Web service operation
     */
    @WebMethod(operationName = "searchByAuthorSurname")
    public List<Book> searchByAuthorSurname(@WebParam(name = "surname") String surname) {
        try {
            List<Book> books = new ArrayList<Book>();
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/BookStoreChainHQDB", "admin", "admin");
            PreparedStatement stmt_aut = con.prepareStatement("select * from AUTHOR where lcase(surname)=?");
            stmt_aut.setString(1, surname.toLowerCase());
            ResultSet res_aut = stmt_aut.executeQuery();
            PreparedStatement stmt_bau = null;
            ResultSet res_bau = null;
            
            while(res_aut.next()) {
                stmt_bau = con.prepareStatement("select * from BOOKAUTHOR where author_id=? ");
                stmt_bau.setInt(1, res_aut.getInt("id"));
                res_bau = stmt_bau.executeQuery();
                
                while(res_bau.next()) {
                    books.add(createCompleteBook(res_bau.getString("book_isbn")));
                }
            }
            
            stmt_aut.close();
            res_aut.close();
            stmt_bau.close();
            res_bau.close();
            
            con.close();
            
            return books;
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.toString());
        } catch (SQLException ex) {
            throw new RuntimeException(ex.toString());
        }
    }
}
